package com.geek688.auxiliarytool.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Enumeration;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSource;
import com.geek688.auxiliarytool.bean.TableInfo;

import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

/**
 * @Title: DBUtil.java
 * @Package: com.nuoyun.util
 * @Description: DBUtil
 * author geek
 * @date: 2019年4月27日
 * @version V1.0
 */
@Slf4j
public class DBUtil {
	
	private String jdbcUrl = "";
	private String username = "";
	private String password = "";
	private static Properties jdbcProperties = null;
	private static DBUtil db = new DBUtil();
//	private String jdbcUrl = "jdbc:mysql://##?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";//新版本链接  6.0
//	private String jdbcUrl = "jdbc:mysql://192.168.1.223/ny_fintech_ol?useUnicode=true&amp;characterEncoding=utf-8";//旧版本链接 5.1
//	private String username = "root";
//	private String password = "root";
	@Getter @Setter
	private static DruidDataSource source = null;
	
	private DBUtil() {

	}
	public static DBUtil getInstance() {
		if (db == null) {
			db = new DBUtil();
		}
		return db;
	}
	public void setParams(String jdbcUrl, String username, String password) {
		this.jdbcUrl = jdbcUrl;
		this.username = username;
		this.password = password;
		this.getSource(jdbcUrl, username, password);
	}
	/**
	 * 获取连接
	 */
	@SuppressWarnings("finally")
	public static Connection getConnection() {
        Connection conn = null;
        try {
			conn = source.getConnection();
		} catch (SQLException e) {
			log.error("数据库连接异常",e);
		} finally {
			return conn;
		}
    }
	/**
	 * 关闭连接
	 */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
        	try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				log.error("关闭连接异常！",e);
			}
        }
    }
    /**
     * 获取全部表
     */
    public static List<String> getAllTables() {
    	List<String> tablesList = new ArrayList<>();
    	Connection conn = null;
    	try {
			conn = DBUtil.getConnection();
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet tables = meta.getTables(null, null, "%", new String[]{"TABLE"});
		    while (tables.next()) {
		    	tablesList.add(tables.getString("TABLE_NAME"));
			}
		} catch (Exception e) {
			log.error("获取信息失败",e);
		} finally {
			DBUtil.closeConnection(conn);
		}
		//按照表名称排序
		String[] strings = new String[tablesList.size()];
		String[] array = tablesList.toArray(strings);
		MSD.sort(array);
//		System.out.println(Arrays.toString(array));
		return tablesList;
	}
    /**
     * 获取指定表名的表结构信息
     */
    public static List<TableInfo> getTableInfo(String tableName) {
    	List<TableInfo> data = new ArrayList<>();
    	Connection conn = null;
    	try {
    		conn = DBUtil.getConnection();
			DatabaseMetaData meta = conn.getMetaData();
	    	data = getTableInfo(meta, tableName,false);
		} catch (Exception e) {
			log.error("获取信息失败",e);
		} finally {
			DBUtil.closeConnection(conn);
		}
		return data;
	}
    public static List<TableInfo> getTableInfo(String tableName,boolean flag) {
    	List<TableInfo> data = new ArrayList<>();
    	Connection conn = null;
    	try {
	    	conn = DBUtil.getConnection();
			DatabaseMetaData meta = conn.getMetaData();
	    	data = getTableInfo(meta, tableName,flag);
		} catch (Exception e) {
			log.error("获取信息失败",e);
		} finally {
			DBUtil.closeConnection(conn);
		}
		return data;
	}
    /**
     * 获取指定表名的表结构信息
     * author geek
     * @date 2019年4月27日
     * @param meta
     * @param tableName
     * @param flag 数据库类型映射为java类型 标志位
     * @return
     */
    public static List<TableInfo> getTableInfo(DatabaseMetaData meta,String tableName,boolean flag) {
    	List<TableInfo> data = new ArrayList<>();
		try {
	    	ResultSet pk = meta.getPrimaryKeys(null, null, tableName);
			ResultSet columns = meta.getColumns(null, null, tableName, null);
			String pkStr = "";
			while (pk.next()) {
				pkStr = pk.getString(4);
			}
			//加载数据类型映射文件
			LinkedList<String> propertyNames = new LinkedList<>();
			Enumeration<?> en = jdbcProperties.propertyNames();
			while(en.hasMoreElements()){
				String propertyName = (String) en.nextElement();
				propertyNames.add(propertyName);
			}
			while (columns.next()) {
				String colName = columns.getString("COLUMN_NAME");
				String colType = columns.getString("TYPE_NAME").toLowerCase();
				String isNull = "0".equals(columns.getString("NULLABLE"))?"NO":"YES";
				String precision = columns.getString("COLUMN_SIZE");
				String defaultVal = (columns.getString("COLUMN_DEF") == null)?"":columns.getString("COLUMN_DEF");
				String remark = columns.getString("REMARKS");
				//去掉注释中的换行符
				remark = remark.replaceAll("(\r\n+)|(\n+)", "");
				if (flag) {
					for(String propertyName:propertyNames){
						String value = (String) jdbcProperties.get(propertyName);
						List<String> values = Arrays.asList(value.split(","));
						if (values.contains(colType)) {
							colType = propertyName;
							break;
						}
					}
				}
				//创建类名，创建规则 按照 _分隔符将每个分割位的首字母大写。注：默认去掉t为开头的表前缀
				//eg:
				//t_use_fund      ---> UseFund  默认去掉前缀t
				//trm_user_basics ---> TrmUserBasics
				//wx_msg_log      ---> WxMsgLog
				String[] tables = tableName.split("_");
	            String clazzName = "";
	            String tablePrefix = "";
	            String tableSubName = "";
				if (tables.length>1) {
					tablePrefix = tables[0];
					tableSubName = "";
					for(int index = 1;index<tables.length;index++){
						tableSubName += tables[index].substring(0, 1).toUpperCase();
						tableSubName += tables[index].substring(1);
					}
	            	int start=(!"t".equals(tables[0]))?0:1;
					for(int index = start;index<tables.length;index++){
						clazzName += tables[index].substring(0, 1).toUpperCase();
						clazzName += tables[index].substring(1);
					}
				}
				data.add(new TableInfo(pkStr,tablePrefix, tableSubName, colName, remark, colType, defaultVal, isNull, tableName, precision, clazzName));
			}
		} catch (Exception e) {
			log.error("获取表结构信息失败",e);
		}
		return data;
	}
    
    public DruidDataSource getSource(String jdbcUrl, String username, String password){
    	if (source == null) {
    		log.debug(this.jdbcUrl+" || "+this.username+" || "+this.password);
			source = new DruidDataSource();
			source.setDriverClassName("com.mysql.jdbc.Driver");//5.1版本
			source.setUrl(jdbcUrl);
			source.setUsername(username);
			source.setPassword(password);
			source.setMaxActive(20);
			source.setMaxWait(5000);
			source.setInitialSize(1);
			source.setMinIdle(1);
			source.setBreakAfterAcquireFailure(true);
			try {
				source.init();
			} catch (SQLException e) {
				log.error("连接池初始化失败",e);
			}
			jdbcProperties = PropertyUtil.loadJdbcProperties();
    	}
		return source;
    }
    
	public void setJdbcUrl(String jdbcUrl) {
		this.jdbcUrl = jdbcUrl;
		source.setUrl(jdbcUrl);
	}
	
	public void setUsername(String username) {
		this.username = username;
		source.setUsername(username);
	}
	
	public void setPassword(String password) {
		this.password = password;
		source.setPassword(password);
	}

}

